package com.wiz.util;

import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;

/**
 * 数据excel导入导出
 * @author xxy
 *
 */
public class ExcelUtil {
	
	public static void export(String SheetName, List<String>headers,List<List<String>>dataList,OutputStream outputStream) throws Exception{
		 // 创建excel
	    HSSFWorkbook work = new HSSFWorkbook();
	
	    // 创建sheet
	    HSSFSheet sheet = work.createSheet(SheetName);
	
	    // 创建一行
	    HSSFRow rowTitle = sheet.createRow(0);
	
	    // 创建标题栏样式
	    HSSFCellStyle styleTitle = work.createCellStyle();
	    styleTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 居中
	    HSSFFont fontTitle = work.createFont();
	    // 宋体加粗
	    fontTitle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
	    fontTitle.setFontName("宋体");
	    fontTitle.setFontHeight((short) 200);
	    styleTitle.setFont(fontTitle);
	    // 列标题及样式
	    HSSFCell cellTitle = null;
	    
	    
	    for(int i = 0;i<headers .size();i++){
	    	cellTitle = rowTitle.createCell(i);
	    	cellTitle.setCellValue(headers.get(i));
	    	cellTitle.setCellStyle(styleTitle);
	    	
	    }
	
	
	    HSSFCellStyle styleCenter = work.createCellStyle();
	    styleCenter.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 居中
	
	    // 取数据
//	    List<AwbDto> data = getData1();
	    HSSFRow row = null;
	    for (int i = 0; i < dataList.size(); i++) {
	    	
	    	List<String>array = dataList.get(i);
	    	
	    	row = sheet.createRow(i + 1);
	    	for(int j = 0;j<array.size();j++){
	    		HSSFCell cell = row.createCell(j);
	    		cell.setCellValue(array.get(j));
	    		cell.setCellStyle(styleCenter);
	    		
	    	}
	    }
	
//	    FileOutputStream fout = new FileOutputStream("d:/awb.xls");
	    work.write(outputStream); 
	    outputStream.flush();  
        outputStream.close(); 
	}
	
	public static List<List<String>> read(InputStream inputStream) throws Exception{
		List<List<String>>dataList = new ArrayList<List<String>>();
		List<String>datas = null;
		 //构建Workbook对象, 只读Workbook对象   
        HSSFWorkbook work = new HSSFWorkbook(inputStream);

        //获取第一张Sheet表   
	    HSSFSheet sheet = work.getSheetAt(0);
        //获取Sheet表中所包含的总列数   
	    int rowNum = sheet.getLastRowNum();
	    for (int i = 1; i <= rowNum; i++) {
          HSSFRow row = sheet.getRow(i);
          if (row != null){
        	  datas = new ArrayList<String>();
        	  int CellNum =row.getLastCellNum();
        	  for(int j = 0; j < CellNum; j++){
        		  row.getCell(j).setCellType(Cell.CELL_TYPE_STRING);
        		  datas.add(row.getCell(j).getStringCellValue());
        		  System.out.println(row.getCell(j).getStringCellValue());
        	  }
        	  dataList.add(datas);
          }
      }
	    return dataList;
	}
	public static void main(String[] args) {
		List<List<String>>values = new ArrayList<List<String>>();
		String[]array =new String []{"编码","名称","年龄"};
		String[]value ={"1","张三","23"};
		String[]value1 ={"1","李四","25"};
		String[]value2 ={"1","李四","25"};
		values.add(Arrays.asList(value));
		values.add(Arrays.asList(value1));
		values.add(Arrays.asList(value2));
		try {
			FileOutputStream out = new FileOutputStream("d:/awb.xls");
			ExcelUtil.export("111", Arrays.asList(array), values,out);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
//		InputStream instream;
//		try {
//			instream = new FileInputStream("d:/awb.xls");
//			ExcelUtil.read(instream);
//		} catch (Exception e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		}
	}

}
